AlchemyJ by Axisoft

5.0

ajSwiftJSONToMX function

Available since AlchemyJ v5.0

Description

The ajSwiftJSONToMX function can convert JSON string to SWIFT MX message. The JSON's keys and values should be matched ISO 20022 standard as below sample string.

{
  "cstmrCdtTrfInitn": {
    "grpHdr": {
      "msgId": "Message-Id",
      "creDtTm": {
        "dateTime": {
          "date": {
            "year": 2024,
            "month": 5,
            "day": 10
          },
          "time": {
            "hour": 16,
            "minute": 10,
            "second": 2,
            "nano": 17000000
          }
        },
        "offset": {
          "totalSeconds": 0
        }
      },
      "nbOfTxs": "1",
      "ctrlSum": 510.24,
      "initgPty": {
        "id": {
          "orgId": {
            "othr": [
              {
                "id": "Client-Id"
              }
            ]
          }
        }
      }
    },
    "pmtInf": [
      {
        "pmtInfId": "Batch-Id",
        "pmtMtd": "TRF",
        "pmtTpInf": {
          "svcLvl": {
            "cd": "SEPA"
          }
        },
        "reqdExctnDt": {
          "year": 2024,
          "month": 5,
          "day": 10
        },
        "dbtr": {
          "nm": "Debtor Account Holder Name"
        },
        "dbtrAcct": {
          "id": {
            "iban": "IBAN"
          }
        },
        "dbtrAgt": {
          "finInstnId": {
            "bic": "Bank BIC"
          }
        },
        "cdtTrfTxInf": [
          {
            "pmtId": {
              "endToEndId": "End-to-End-Id"
            },
            "amt": {
              "instdAmt": {
                "value": 510.24,
                "ccy": "EUR"
              }
            },
            "cdtrAgt": {
              "finInstnId": {
                "bic": "Bank BIC"
              }
            },
            "cdtr": {
              "nm": "Creditor Account Holder Name"
            },
            "cdtrAcct": {
              "id": {
                "iban": "IBAN"
              }
            }
          }
        ]
      }
    ]
  },
  "type": "MX",
  "@xmlns": "urn:iso:std:iso:20022:tech:xsd:pain.001.001.03",
  "identifier": "pain.001.001.03"
}

Syntax

ajSwiftJSONToMX(json_data,[run_condition],[run_by_function_point_only])

Argument NameArgument TypeDescription
json_data
(required)		RangeSpecify content of the JSON from a range of cells. The JSON can span multiple rows and columns, and the content in cells are concatenated into one string from left to right and top to bottom.
run_condition
(optional)		BooleanThe function will run when the value is TRUE. Otherwise will not run.
The default value is TRUE.
run_by_function_point_only
(optional)		BooleanIf it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point.

If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only.


The default value is TRUE.

The function will return:

1) Return Value: SWIFT MX message string.

2) Return Type: Single Value / Multiple values (array formula).

Example

Make sure the AlchemyJ Function Proxy was started up when executing ajSwiftJSONToMX in the AlchemyJ workbook. You can start the proxy from More Tools - Run AlchemyJ Function Proxy.

We use the above JSON string convert to Swift MX message.

=ajSwiftJSONToMX(B1,B2,B3)

The result as below

<?xml version="1.0" encoding="UTF-8" ?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03">
    <CstmrCdtTrfInitn>
        <GrpHdr>
            <MsgId>Message-Id</MsgId>
            <CreDtTm>2024-05-10T16:10:02.017+00:00</CreDtTm>
            <NbOfTxs>1</NbOfTxs>
            <CtrlSum>510.24</CtrlSum>
            <InitgPty>
                <Id>
                    <OrgId>
                        <Othr>
                            <Id>Client-Id</Id>
                        </Othr>
                    </OrgId>
                </Id>
            </InitgPty>
        </GrpHdr>
        <PmtInf>
            <PmtInfId>Batch-Id</PmtInfId>
            <PmtMtd>TRF</PmtMtd>
            <PmtTpInf>
                <SvcLvl>
                    <Cd>SEPA</Cd>
                </SvcLvl>
            </PmtTpInf>
            <ReqdExctnDt>2024-05-10</ReqdExctnDt>
            <Dbtr>
                <Nm>Debtor Account Holder Name</Nm>
            </Dbtr>
            <DbtrAcct>
                <Id>
                    <IBAN>IBAN</IBAN>
                </Id>
            </DbtrAcct>
            <DbtrAgt>
                <FinInstnId>
                    <BIC>Bank BIC</BIC>
                </FinInstnId>
            </DbtrAgt>
            <CdtTrfTxInf>
                <PmtId>
                    <EndToEndId>End-to-End-Id</EndToEndId>
                </PmtId>
                <Amt>
                    <InstdAmt Ccy="EUR">510.24</InstdAmt>
                </Amt>
                <CdtrAgt>
                    <FinInstnId>
                        <BIC>Bank BIC</BIC>
                    </FinInstnId>
                </CdtrAgt>
                <Cdtr>
                    <Nm>Creditor Account Holder Name</Nm>
                </Cdtr>
                <CdtrAcct>
                    <Id>
                        <IBAN>IBAN</IBAN>
                    </Id>
                </CdtrAcct>
            </CdtTrfTxInf>
        </PmtInf>
    </CstmrCdtTrfInitn>
</Document>

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
The format of JSON string is incorrect.
JSON‘s keys or values don't meet ISO 20022 standard.
← ajSwiftMTToJSONajSwiftMXToJSON →